#!/usr/bin/env python3
# Database interaction module

'''
A module of classes to interface with an SQLite database.
'''

import sqlite3
import collections
import csv
import re

class Database():
    '''
    Database object to interact with a SQLite database and a GUI.
    '''
    def __init__(self):
        '''
        self.connection is the connection to the database we want to edit.
        self.cursor is an object holding the data from the database we have asked for.
        self.tableSchema is built in getSchema() to hold the critical information
        about fields that we need.
        '''
        self.connection = None
        self.cursor = None
        self.tableSchema = None
        self.schemaList = None

    def openDB(self,filename):
        '''
        Establishes the connection to a database.

        We check that the file is an actual SQLite database. If it is, we make
        the connection and return True. If it is not, we raise an exception.
        '''
        if self.isFileSQLite(filename):
            self.connection = sqlite3.connect(filename)
            self.cursor = self.connection.cursor()
            return True
        else:
            raise InvalidDatabaseException()
        """
        The InvalidDataBaseException() is an exception we created ourselves -
        as you can see in the class below. By giving it our own name and raising
        it here when a file is not the proper database, it allows us to pass this
        specific exception back to the GUI to indicate that the file is invalid.
        We catch the exception in the GUI and let the user know that the file is
        invalid. It enables the programmer to define an error and pass it back.
        We could have left out the exception, in which case openDB() would
        return False and we could check for that in the GUI rather than the exception.

        We need to explain this in the book.
        """

    def isFileSQLite(self,filename):
        '''
        Method to open a file as binary and reads the first 15 bytes. If they
        correspond to the header of a SQLite file, returns True. 
        '''
        f = open(filename, 'rb')
        #we read out the first 15 bytes decoded to utf-8 encoding
        try:
            dbHeading = f.read(15).decode()
            #close the file
            f.close()
            #check that it corresponds to a SQLite file header
            if dbHeading == 'SQLite format 3': 
                return True
        #trap files with the wrong encoding - Note that this simply tells the
        #user the file is not a valid database and does not tell him that
        #it may have been corrupted or that the encoding is wrong
        except UnicodeDecodeError as err:
            f.close()
            return False
        """
        I got the idea and basic coding from this site:
        http://stackoverflow.com/questions/12932607/how-to-check-with-python-and-sqlite3-if-
        one-sqlite-database-file-exists. The file has to be opened with the 'b'
        option which opens it as bytes rather than text. When we read the first
        15 bytes, we need to decode them which should give us the "SQLite format
        3" given above. A corrupted file won't be read at all, and in order to
        trap that error I added the exception above. The end user will only know
        that it is not a valid database file. He won't be told it might be corrupt.
        There are other instances in which a file can't be read and will throw an error -
        but we probably need to read the error.
        """

    def listTables(self):
        '''
        Method to give a list of the tables in a database
        '''
        #reset self.tableSchema so the new table will get it again
        self.tableSchema = None
        dbTables = self.cursor.execute('''SELECT name FROM sqlite_master WHERE type='table'
                and name!='sqlite_sequence' ORDER BY name;''')
        tblList = []
        for tuple in dbTables:
            tblList.append(tuple[0])
        return tblList
    
        """
        The sqlite_master is a table in every SQLite database containing, among
        other things, a list of the tables in the database (except itself). We exclude
        sqlite_sequence from the list as it is generated by sqlite to track rowid where
        the database creator has implemented a unique primary key in the database.
        """

    def closeDB(self):
        '''
        Closes the connection to the database
        '''
        self.connection.close()

    def getSchema(self,tableName):
        '''
        Determine the fields and constraints in a table and return them as a list.

        Currently using self.tableSchema which uses information from built-in table_info.
        This may be all that is needed for most applications. Hope to eventually parse out
        every constraint from table schema.
        '''
        # Get basic schema information in a list of tuples.
        self.tableSchema = [tuple for tuple in self.cursor.execute('PRAGMA table_info(' + tableName + ');')]

        # Parse out entries from actual table schema. We use this to detect constraints
        # not given us in the basic schema information.
        sql = self.cursor.execute('''SELECT sql FROM sqlite_master WHERE type='table'
                and name=?;''',(tableName,))
        schemaList = []
        for item in sql:
            itemtxt = (str(item))
            itemtxt = itemtxt.replace('\n','')
            ind = 0
            for i in self.tableSchema:
                loc1 = re.search(i[1],itemtxt)
                loc2 = self.locEnd(ind,itemtxt)
                if self.locEnd(ind,item):
                    text = itemtxt[loc1.start():loc2.start()]
                else:
                    text = itemtxt[loc1.start():]
                ind = ind + 1
                schemaList.append(text)
        self.schemaList = schemaList
        """
        Here we begin the parsing process of the entire schema from the database table. By using self.tableSchema, which
        gives us the names of the fields and some information about them, we are able to locate the beginning of each
        field description (using the module re - regular expressions - and a search function there, which returns the
        beginning location of the string) and separate it out from the others. This gives us one line for each field,
        which we can then parse further.
        """

    def locEnd(self,ind,item):
        '''
        This method simply finds the beginning of the next field in our table schema so we can mark the extent of the current field.
        When we come to the last field, we will get an IndexError, so the final field extends to the end of the schema.
        '''
        try:
            loc = re.search(self.tableSchema[ind+1][1],str(item))
            return loc
        except IndexError:
            return None

    def newRecord(self,tableName):
        '''
        Returns a new blank record for the database.
        '''
        
        if not self.tableSchema:
            self.getSchema(tableName)
        # We build the record - using the Record class which gives us an ordered dictionary
        record = self.buildRecord()
        return record

    def addRecord(self,record,tableName):
        '''
        Add a record, passed by the gui, to the database. 
        '''
        values1 = []
        fields1 = []
        markers = ""
        for k, v in record.items():
            fields1.append(k)
            values1.append(v.value)
            markers = markers + '?,'
        markers = markers[:-1]
        fields = tuple(fields1)
        values = tuple(values1)
        sql = "INSERT INTO " + tableName + str(fields) + " VALUES (" + markers + ")"
        self.cursor.execute(sql,values)
        self.connection.commit()
        """
        The record consists of an ordered dictionary with the field names as keys
        and a Field object as the value. In order to build the sqlite query, we need
        both the values to put in the new record and the right number of "?" placeholders
        to match the number of fields. Thus we build a list of field values (extracting
        the value from the Field object) which we turn into a tuple to use in the query.
        We also build a string of "?," and then remove the last comma with a slice. It is
        not necessary to use the semi-colon and since we're committing a new record with
        a value for every field, we don't need to name the fields in our query.
        """
        
    def deleteRecord(self,tablename,rowid):
        sql = 'DELETE FROM ' + tablename + ' WHERE ROWID = ?'
        rowid = (rowid,)
        self.cursor.execute(sql,rowid)
        return True

    def updateRecord(self,record,tableName,rowid):
        '''
        Update an existing record in the database with the record, tablename and rowid
        passed to us from the gui.

        Build an sql query using each field in the record and execute that query in
        order to update an existing record.
        '''

        # Initial base for the query
        sql = "UPDATE " + tableName + " SET "
        # Iterate through the record items to get the data to build the query field by field.
        for k, v in record.items():
            # Check to see if the type is an integer
            if v.type.upper() == 'INTEGER':
                # Check to see if we have a NULL value and if so, skip this field
                if v.value == 'None':
                    pass
                else:
                    sql += k + " = " + v.value + ", "
            else:
                # Check to see if we have a NULL value and if so, skip this field
                if v.value == 'None':
                    pass
                else:
                    sql += k + " = '" + v.value + "', "
        # split off the final comma and space    
        sql = sql[:-2]
        # add the rowid so we know which record to update
        sql = sql + " WHERE ROWID = " + str(rowid)
        self.cursor.execute(sql)
        self.connection.commit()

        """
        In the query statement, integers don't need quotes, whereas strings do, so we build
        they query a little differently for different types. Each field adds another part
        to the query statement - we are simply concatenating strings. We have to check to
        see if the field contains 'None', which means it had a NULL value that has not been
        changed. We cannot sent 'None' back to the database (we get an IntegrityError and
        it would change the value of the field if it worked), so we skip the fields that
        contain 'None'. When we have our complete statement, we can execute it and commit
        it so that the databse is changed. until this point, we have not touched the
        database itself.
        """

    def viewRecord(self,tableName,rowid):
        '''
        Show a record identified by rowid.

        This pulls a particular record, identified by the rowid, from the table and
        returns it to the gui so it can be updated, etc.
        '''
        if not self.tableSchema:
            self.getSchema(tableName)
        row = self.__createRecord(tableName,rowid)
        record = self.buildRecord(row)
        return record

    def exportTable(self,file,table):
        self.cursor.execute('SELECT * from ' + table)
        csvWriter = csv.writer(open(file,'wt'))
        csvWriter.writerow([i[0] for i in self.cursor.description])
        csvWriter.writerows(self.cursor)
        del csvWriter

    def selectColumn(self,tablename):
        '''
        Get a list of the field names from the table.
        '''
        self.cursor.execute('SELECT * FROM ' + tablename)
        colNameList = [tuple[0] for tuple in self.cursor.description]
        return colNameList

        """
        This method extracts a list of all of the field names from any table name
        given to it. This is used to populate a combobox to give the user a choice
        of fields from which to select values.
        """

    def selectValue(self,tablename,field):
        '''
        Extract all possible values from a table for a particular field.
        '''
        self.cursor.execute('SELECT ' + field + ' FROM ' + tablename\
                            + ' WHERE ' + field + ' IS NOT NULL')
        select1 = self.cursor.fetchall()
        select = [tuple[0] for tuple in select1]
        select = self.removeDupes(select)
        select.sort()
        return select

        """
        The sqlite select statement pulls up all the values, except any NULL values
        (i.e., the field is blank) for a field we have passed to it from the
        gui, which the user has chosen. This is used to populate a combobox so
        the user can choose from existing values.
        """

    def selectValue2(self,tablename,field,field1,value):
        '''
        Returns possible values for a chosen field based on the value chosen for
        a first field.
        '''
        self.cursor.execute('SELECT ' + field + ' FROM ' + tablename\
                            + ' WHERE ' + field1 + ' = "' + value + '"'\
                            + ' AND ' + field + ' IS NOT NULL')
        select1 = self.cursor.fetchall()
        select = [tuple[0] for tuple in select1]
        select.sort()
        return select

        """
        When the user has already chosen a value for one field and has chosen a
        second field in order to make a selection of a particular record, this method
        returns the possible values for that second field to make the selection final.
        """

    def removeDupes(self,ls):
        '''
        Removes duplicates in a list
        '''
        noDupes = []
        for x in ls:
            if x not in noDupes:
                noDupes.append(x)
        return noDupes

    def __createRecord(self,tableName,recordId):
        '''
        Return a row from the database.

        We get a tableName and recordId from the user, make a SQLite query using that data,
        and return the corresponding row from the table.
        '''
        
        self.cursor.execute('SELECT * FROM ' + tableName + ' WHERE rowid=?;',(recordId,))
        row = self.cursor.fetchone()
        return row

    def getRowid(self,tablename,field1,field1Value,field2,field2Value):
        '''
        Calculates rowid from user's choice of two fields and their values
        '''
        sql = 'SELECT rowid FROM ' + tablename + ' WHERE ' + field1 + ' = "' +\
              field1Value + '" AND ' + field2 + ' = "' + field2Value + '"'
        self.cursor.execute(sql)
        rowid = self.cursor.fetchone()[0]
        return rowid

        """
        This method builds a query to find the rowid using the values the user has
        selected from two fields. The combination of the two fields used should make
        the selection of a record exclusive, otherwise it will select the first record
        matching those values. The fields used could be hardcoded into any particular
        instance of this program, bypassing the user's ability to choose the fields.
        We do it this way to make it generic and usable for any table.
        """
        
    def buildRecord(self,row=None):
        '''
        Build the the record using field names as keys and field objects as values.

        From the record, an ordered dictionary, we can get what we need to know about each field.
        '''
        # instantiate the record object - which is an ordered dictionary
        record = Record()
        for i in self.tableSchema:
            if row:
                # create the field object (with a value)
                field = Field(self.tableSchema,str(i[1]),self.schemaList,str(row[self.tableSchema.index(i)]))
            else:
                # create the field object (without a value)
                field = Field(self.tableSchema,str(i[1]),self.schemaList)
            # add the field to the dictionary
            record[str(i[1])] = field
        return record

        """
        We have a raw record from the table, but we want to have complete information about
        each field, not just its value. The ordered dictionary we build matches information from
        the table's schema about each field with the field name to create a field object. We are
        using a sort of generator to create the ordered dictionary, which is repeated for each
        field in the record. If there is no row from the database, we are creating an empty
        record with all the field information that permits us to populate it correctly.
        """

class InvalidDatabaseException(Exception): pass    

class Record(collections.OrderedDict):

    def add(self,fieldName,data):
        '''
        Change or set the value for any field in a record
        '''
        self[fieldName].value = data
        
    
'''
An ordered dictionary object of the fields in a record.

At this point, the Record class is simply an OrderedDict class without any changes. Unless we have
a reason to add methods to this class, we may not even need the class. We will see. We are building
the ordered dictionary in the buildRecord() method above.
'''        

class Field():
    '''
    Access and extract information from fields to build and/or change records.

    Fields are our basic building block.
    '''
    def __init__(self,tableSchema,fieldName,schemaList,value=None):
        self.name = fieldName
        self.tableSchema = tableSchema
        self.schemaList = schemaList
        self.value = value
        self.index = self.findFieldIndex()
        self.type = self.tableSchema[self.index][2]
        self.notNull = self.tableSchema[self.index][3]
        self.defaultValue = self.tableSchema[self.index][4]
        self.isPrimaryKey = self.tableSchema[self.index][5]
        self.autoIncrement = self.isAutoIncrement()

        
    def findFieldIndex(self):
            for i in self.tableSchema:
                if str(i[1]) == self.name:
                    return i[0]

    def isAutoIncrement(self):
#        print(str(self.schemaList))
        if 'AUTOINCREMENT' in str(self.schemaList[self.index]).upper():
            return True
        else:
            return False


    


